<?php
header("Content-Type: text/html; charset=iso-8859-1");
require('include.php');
$_SESSION['navigation'] = 'membres';
$user_id = check_cookie();
$order = (!empty($_REQUEST['order'])) ? $_REQUEST['order'] : "nom";
if(preg_match("/(.*)\ (.*)/",$order,$regs)){
	$order = ($regs[2] == 'asc') ? $regs[1]." desc" : $regs[1]." asc";
}

$action = (!empty($_REQUEST['action'])) ? $_REQUEST['action'] : "";
$start = (!empty($_REQUEST['start'])) ? $_REQUEST['start'] : 1;
$ancien = (!empty($_REQUEST['ancien'])) ? $_REQUEST['ancien'] : 0;
$id = (!empty($_REQUEST['id'])) ? $_REQUEST['id'] : "";
if(isset($_REQUEST['filtre_prenom']) && isset($_SESSION['filtre_prenom'])) unset($_SESSION['filtre_prenom']);
if(isset($_REQUEST['filtre_nom']) && isset($_SESSION['filtre_nom'])) unset($_SESSION['filtre_nom']);
$filtre_prenom = (!empty($_REQUEST['filtre_prenom'])) ? $_REQUEST['filtre_prenom'] : "";
$filtre_titre = (!empty($_REQUEST['filtre_titre'])) ? $_REQUEST['filtre_titre'] : "";
#$_SESSION['filtre_prenom'] = ($filtre_prenom) ? $filtre_prenom : $_SESSION['filtre_prenom'];
#$_SESSION['filtre_nom'] = ($filtre_nom) ? $filtre_nom : $_SESSION['filtre_nom'];
if($action != 'export') header("Content-Type: text/html; charset=iso-8859-1");
if($action == 'demission'){
	if(check_admin("commission_technique")){
		$tables = array("membre_commissions","membre_equipes","entraineurs");
		foreach($tables as $table){
			$q = "delete from $table where membre_id = '$id'";
			ExecRequete($q);
		}
		$tabs = array("responsable");
		foreach($tabs as $tab)	{
			$q = "update equipes set $tab = '' where $tab = '$id'";
			ExecRequete($q);
		}
	}
	else{
		redirect('perso.php');
	}
}
elseif($action == 'new'){
	$membre = new membre(0);
	$membre->print_detail(1);
	$membre->print_admin();
}
elseif($action == 'form_export'){
	if(check_admin("commission_technique")){
		$equipes = liste('equipes',"id_vbcll","num");
		$others = array("arbitres" => "arbitres", "marqueurs" => "tous les marqueurs", "marqueurs_sans" =>
		"marqueurs non inscrits","tous" =>"<span class = 'bold'>TOUS</span>");
		$categories = $equipes+$others;
		echo "<form action = 'membre_admin.php' name = 'myform' method = 'post'>\n";
		echo "<h2>S&eacute;l&eacute;ctionner les cat&eacute;gories de membres</h2>";
		echo "<p class = 'center'><ul class = 'float'>\n";
		foreach($categories as $id => $print){
			$class = 'fade';
			echo "<li class = 'float' ><input type = 'checkbox' name = '$id' value = '1' onclick = 'if(this.checked) document.getElementById(\"cat$id\").className = \"bold\"; else document.getElementById(\"cat$id\").className = \"fade\"' /><span id = 'cat$id' class = '$class'>$print</span></li>\n";
		}
		echo "</ul></p>\n";
		echo "<br class = 'clear' />\n";
		echo "<h2>Choisir les champs &agrave; exporter</h2>\n";
		$fields = describe('membres');
		$fields['equipes'] = 'varchar(255)';
		$fields['compta_cotisations'] = 'varchar(255)';
		$fields['compta_licence'] = 'varchar(255)';
		echo "<p class = 'center'><ul class = 'float'>\n";
		foreach($fields as $name => $type){
			$checked = ($name == 'nom' || $name == 'prenom') ? "checked = 'checked'" : "";
			$class = ($checked) ? "bold" : "fade";
			echo "<li class = 'float'><input type = 'checkbox' name = '$name' value = '1' $checked  onclick = 'if(this.checked) document.getElementById(\"field$name\").className = \"bold\"; else document.getElementById(\"field$name\").className = \"fade\"' /><span id = 'field$name' class = '$class'>".str_replace("_"," ",$name)."</span></li>\n";
		}
		echo "</ul></p>\n";
		echo "<br class = 'clear' />\n";
		echo "<p class = 'center'><button type = 'submit' name = 'submit'><img src = 'images/icon_download.png' alt = '' width = '14' height = '14' />exporter</button>&nbsp;<button type = 'button' onclick = 'list_membres(1,1)'><img src = 'images/icon_list.png' alt = '' width = '14' height = '14' />retour &agrave; la liste</button><input type = 'hidden' name = 'action' value = 'export' /></p>\n";
		echo "</form>";
	}
	else redirect('perso.php');
}
elseif($action == 'export'){
	if(!check_admin("commission_technique")) redirect('perso.php');
	else{
		require_once "include/php_writeexcel/class.writeexcel_workbook.inc.php";
		require_once "include/php_writeexcel/class.writeexcel_worksheet.inc.php";
		$fname = tempnam("tmp/","vbcll_export.xls");
		$workbook = new writeexcel_workbook($fname);
		$worksheet = $workbook->addworksheet();
		$heading = $workbook->addformat(array('align' => 'center', 'bold' => 1));
		$equipes = liste('equipes',"id_vbcll","num");
		$others = array("arbitres" => "arbitres", "marqueurs" => "tous les marqueurs", "marqueurs_sans" =>
		"marqueurs non inscrits","tous" =>"<span class = 'bold'>TOUS</span>");
		$categories = $equipes+$others;
		$fields = describe('membres');
		$selected = array();
		$list_fields = "";
		$les_membres = array();
		foreach($categories as $id => $print){
			if(!empty($_REQUEST[$id])){ $selected[$id] = $id;}
		}
		$col = 0;
		$print_fields = array();
		foreach($fields as $field => $type){
			if(!empty($_REQUEST[$field])){
				$worksheet->write(0, $col,  strtoupper($field),$heading);
				$col++;
				$print_fields[] = "membres.".$field." as $field";
			}
		}
		$list_fields = implode(", ",$print_fields);
		if(!empty($_REQUEST['marqueurs_sans'])){
			$worksheet->write(0, $col,  'NB MARQUAGES',$heading);
			$col++;
		}
		if(!empty($_REQUEST['equipes'])){
			$worksheet->write(0, $col,  "EQUIPES",$heading);
			$print_equipes = 1;
		}
		else {$print_equipes = 0; $col--;}
		if(!empty($_REQUEST['compta_cotisations'])){
			$col++;
			$worksheet->write(0, $col,  "TARIF COTIS.",$heading);
			if(empty($_REQUEST['compta_licence'])){
				$col++;
				$worksheet->write(0, $col,  "DATE PAYEMENT",$heading);
			}
			$print_cotisations = 1;
		}
		if(!empty($_REQUEST['compta_licence'])){
			$col++;
			$worksheet->write(0, $col,  "TARIF LICENCE",$heading);
			$col++;
			$worksheet->write(0, $col,  "DATE LICENCE",$heading);
			$print_licence = 1;
		}
		if($list_fields){
			if(!empty($selected['tous'])){
				$q = "select membres.id as id,$list_fields,comptabilite.licence as compta_licence, comptabilite.cotisation as compta_cotisation, comptabilite.tarif_cotisation as  tarif_cotisation, comptabilite.tarif_licence as tarif_licence, comptabilite.date_payement as date_payement from membres left join comptabilite on membres.id = comptabilite.membre_id where valide = 1 and fictif = 0";
				$r = ExecRequete($q);
				$row = 1;
				while($s = mysql_fetch_row($r)){
					$id = array_shift($s);
					if(!in_array($id,$les_membres)){
						$col = 0;
						for($col = 0; $col < count(explode(",",$list_fields)); $col++){
							$data = $s[$col];
							$worksheet->write($row, $col, $data);
						}
						if($print_equipes){
							$membre = new membre($id);
							$worksheet->write($row, $col,  implode(", ",$membre->equipes));
						}
						else $col--;
						if($print_cotisations || $print_licence){
							$date_payement = array_pop($s);
							$tarif_licence = array_pop($s);
							$tarif_cotisation = array_pop($s);
							$cotis = array_pop($s);
							$licence = array_pop($s);
							if($print_cotisations){
								$col++;
								$data = ($cotis) ? $tarif_cotisation : 0;
								$worksheet->write($row, $col, $data);
							}
							if($print_licence){
								$col++;
								$data = ($cotis) ? $tarif_licence : 0;
								$worksheet->write($row, $col, $data);
							}
							$col++;
							$worksheet->write($row, $col, $date_payement);
						}
						$row++;
						$les_membres[] = $id;
					}
				}
			}
			else{
				$row = 1;
				foreach($equipes as $id => $id_vbcll){
					if(!empty($selected[$id])){
						$equipe = new equipe($id);
						$membres = $equipe->list_membres();
						foreach($membres as $membre_id => $membre){
							if(!in_array($membre_id, $les_membres)){
								$q = "select membres.id as id,$list_fields,comptabilite.licence as compta_licence, comptabilite.cotisation as compta_cotisation, comptabilite.tarif_cotisation as  tarif_cotisation, comptabilite.tarif_licence as tarif_licence, comptabilite.date_payement as date_payement from membres left join comptabilite on membres.id = comptabilite.membre_id where membres.id = $membre_id and valide = 1 and fictif = 0";
								$r = ExecRequete($q);
								while($s = mysql_fetch_row($r)){
									$id = array_shift($s);
									$col = 0;
									for($col = 0; $col < count(explode(",",$list_fields)); $col++){
										$data = $s[$col];
										$worksheet->write($row, $col, $data);
									}
									if($print_equipes){
										$membre = new membre($id);
										$worksheet->write($row, $col,  implode(", ",$membre->equipes));
									}
									else $col--;
									if($print_cotisations || $print_licence){
										$date_payement = array_pop($s);
										$tarif_licence = array_pop($s);
										$tarif_cotisation = array_pop($s);
										$cotis = array_pop($s);
										$licence = array_pop($s);
										if($print_cotisations){
											$col++;
											$data = ($cotis) ? $tarif_cotisation : 0;
											$worksheet->write($row, $col, $data);
										}
										if($print_licence){
											$col++;
											$data = ($cotis) ? $tarif_licence : 0;
											$worksheet->write($row, $col, $data);
										}
										$col++;
										$worksheet->write($row, $col, $date_payement);
									}
									$row++;
									$les_membres[] = $membre_id;
								}
							}
						}
					}
				}
				if(!empty($selected['arbitres'])){
					$q = "select id,$list_fields from membres where (arbitre = 1 or arbitre_junior = 1) and valide = 1 and fictif = 0";
					$r = ExecRequete($q);
					while($s = mysql_fetch_row($r)){
						$id = array_shift($s);
						if(!in_array($id,$les_membres)){
							$col = 0;
							foreach($s as $data){
								$worksheet->write($row, $col, $data);
								$col++;
							}
							if($print_equipes){
								$membre = new membre($id);
								$worksheet->write($row, $col,  implode(", ",$membre->equipes));
							}
							$row++;
							$les_membres[] = $id;
						}
					}
				}
				if(!empty($selected['marqueurs'])){
					$q = "select id,$list_fields from membres where marqueur = 1 and valide = 1 and fictif = 0";
					$r = ExecRequete($q);
					while($s = mysql_fetch_row($r)){
						$id = array_shift($s);
						if(!in_array($id,$les_membres)){
							$col = 0;
							foreach($s as $data){
								$worksheet->write($row, $col, $data);
								$col++;
							}
							if($print_equipes){
								$membre = new membre($id);
								$worksheet->write($row, $col,  implode(", ",$membre->equipes));
							}
							$row++;
							$les_membres[] = $id;
						}
					}
				}
				elseif(!empty($selected['marqueurs_sans'])){
					$q = "select membres.id as id,$list_fields, count(distinct matchs.no) as nb from membres left join matchs on membres.id = matchs.marqueur1 left join `membre_commissions` on membres.id = `membre_commissions`.`membre_id` inner join membre_equipes on membres.id = membre_equipes.`membre_id` inner join equipes on equipes.id = membre_equipes.equipe_id where marqueur = 1 and arbitre = 0 and membres.valide = 1 and membres.fictif = 0 and `membre_commissions`.id is null and equipes.nom != 'LNA'  group by membres.id";
					$r = ExecRequete($q);
					while($s = mysql_fetch_row($r)){
						$id = array_shift($s);
						$membre = new membre($id);
						if($s[count($s)-1] >= 2 || in_array("LNA",$membre->equipes)) continue;
						if(!in_array($id,$les_membres)){
							$col = 0;
							foreach($s as $data){
								$worksheet->write($row, $col, $data);
								$col++;
							}
							if($print_equipes){

								$worksheet->write($row, $col,  implode(", ",$membre->equipes));
							}
							$row++;
							$les_membres[] = $id;
						}
					}

				}
			}
			$workbook->close();
			header("Content-Type: application/x-msexcel; name=\"vbcll_membres_export.xls\"");
			header("Content-Disposition: attachment; filename=\"vbcll_membres_export.xls\"");
			$fh=fopen($fname, "rb");
			fpassthru($fh);
			unlink($fname);
		}
	}
}
elseif($action == 'list'){
	$table = new table();
	$table->nb_rows = 40;
	$fields = array("id" => "id",
					"prenom" => "pr&eacute;nom",
					"nom" => "nom",
					"equipes" => "&eacute;quipe(s)",
					"admission" => "admission",
					"actions" => "actions");
	foreach($fields as $field => $print){
		$$field = new table_field($field);
		$$field->print = $print;
		$table->fields[] = $$field;
		if($field == 'actions'){
			$$field->styleclass = 'center';
		}
		if($field == 'id') $$field->hidden = 1;
	}
	$equipes->filter = 0;
	$table->styleclass = 'bold_hover';
	$table->filter = 1;

	$q = "select membres.id as membre, equipes.nom as equipe from membres left join membre_equipes on membres.id = membre_id inner join equipes on equipes.id = equipe_id order by membre_id";
	$r = ExecRequete($q);
	while($s = LigneSuivante($r)){
		${$s->membre}[] = $s->equipe;
	}

	$q = "select membres.id as id, prenom,membres.nom as nom ,valide,membres.note as note,admission, group_concat(equipes.nom separator ', ') as equipes from membres left join membre_equipes on membres.id = membre_id left join equipes on equipes.id = equipe_id group by membres.id order by $order ";
	$r = ExecRequete($q);
	while($s = LigneSuivante($r)){
		$row = new table_row();
		$row->onclick = "";
		if((!$s->valide && !$ancien) || ($s->valide && $ancien)) $row->hidden = 1;
		foreach($fields as $field => $print){
			if($field == 'actions'){
				$row->cols[$field] = "<a href = 'javascript:self.location.href = \"membre_admin.php?id=$s->id&action=complet\"'><img src = 'images/icon_edit.png' alt = 'edit' width = '14' height = '14'/></a>";
				if($s->valide)	$row->cols[$field] .= "<a href = 'javascript:if(confirm(\"Vraiment supprimer $s->prenom $s->nom ?\")){demission($s->id,1);}'><img src = 'images/icon_delete.png' alt = 'supprimer' width = '14' height = '14' /></a>";
				else $row->cols[$field] .= "<a href = 'javascript:if(confirm(\"Vraiment reintegrer $s->prenom $s->nom ?\")){demission($s->id,0);}'><img src = 'images/icon_reset.png' alt = 'supprimer' width = '14' height = '14' /></a>";
			}
			else{
				if($field == 'equipes'){
					$echo = (isset(${$s->id}) && count(${$s->id})) ? implode(", ",${$s->id}) : "";
					if(!$echo && $s->note) $echo = $s->note;
				}
				else{
					$echo = CapitalizeLastName(strtolower($s->$field));
				}
				$row->cols[$field] = $echo;
			}
		}
		$table->rows[] = $row;
	}
	if(check_admin("commission_technique")){
		if(!$ancien){
			echo "<p class = 'center'><button type = 'button' onclick = 'new_membre(1)'><img src = 'images/icon_new.png' alt = '' width = '14' height = '14' />nouveau</button>&nbsp;<button type = 'button' onclick = 'export_membres()'><img src = 'images/icon_download.png' alt = '' width = '14' height = '14' />exporter</button></p>\n";
			echo "<p class = 'small' style = 'text-align: right; font-size: 7pt; line-height: 7pt; padding-bottom: 0; margin-bottom: -15px;margin-right: 20px;'>";
			echo "<a href = 'javascript: list_membres(1,1,1)'>afficher les anciens membres</a></p>\n";
		}
		else echo "<p class = 'small' style = 'text-align: right; font-size: 7pt; line-height: 7pt; padding-bottom: 0; margin-bottom: -15px;margin-right: 20px;'><a href = 'javascript: list_membres(1,1)'>afficher les membres actuels</a></p>\n";
	}
	echo $table->print_header();
	echo $table->print_body();
	echo $table->print_footer();
}
else{
	$ok = 1;
	entete("VBCLL membres","","",1);
	if($action == 'complet'){
		$membre = new membre($id);
		echo "<h1>".(CapitalizeLastName(strtolower($membre->prenom." ".$membre->nom)))."</h1>\n";
		$membre->print_detail(1);
		if(check_admin("commission_technique")) echo "<p class = 'center'><button type = 'button' onclick = 'self.location.href=\"inscriptions.php?id=$id&step=4\"'><img src = 'images/icon_pdf_small.png' alt = 'pdf' width = '14' height = '14' />formulaire d'inscription</button></p>\n";
		$membre->print_admin();
	}
	else{
		echo "<h1>Les membres</h1>\n";
		// echo "<div class = 'hidden' style = 'margin-left: 60px; height: 150px' id = 'membre_details'>\n";
		// box("<div id = 'nom'></div>","<div id = 'details'></div>",500,1);
		// echo "</div>\n";
		if($action == 'add_new'){
			foreach($_POST as $key => $val){
				$$key = $val;
			}
			$message = "";
			$required = array("titre","nom","prenom","adresse","localite","npa","ddn_jour","ddn_mois","ddn_annee");
			$champs_num = array("npa","ddn_jour","ddn_mois","ddn_annee");
			foreach($required as $champ){
				if(!isset($$champ)) $message .= $champ." ne peut pas être vide<br />";
				elseif(!$$champ) $message .= $champ." ne peut pas être vide<br />";
				else{
					if(in_array($champ,$champs_num) && preg_match("/[^0-9]/",$$champ)) $message .= $champ." doit être numérique<br />";
				}
			}
			if(isset($email)){
				if($email){
					if(!ereg(".*@.*\.[a-z]*",$email)) $message .= "l'adresse email n'est pas valide<br />";
				}
			}
			if(!isset($commune)) $commune = $localite;
			elseif(!$commune) $commune = $localite;
			if($ddn_mois > 12 || $ddn_annee > (date('Y') - 8 || $ddn_jour > 31)) $message .= "La date de naissance n'est pas valide";
			if($message){
				echo "<p class = 'erreur'>$message</p>\n";
				echo "<p class = 'center'><button type = 'button' onclick = 'self.history(-1)'>corriger...</button></p>\n";
			}
			else{
				$ddn = $ddn_annee."-".$ddn_mois."-".$ddn_jour;
				$fields = describe('membres');
				$membre = new membre(0);
				foreach($fields as $field => $type){
					$value = (isset($$field)) ? $$field : "";
					$membre->$field = $value;
				}
				$q = "select id,nom from equipes";
				$r = ExecRequete($q);
				while($s = LigneSuivante($r)){
					if(isset(${"equipe_".$s->id})) $membre->equipes[$s->id] = $s->nom;
				}
				$q = "select id, nom from commissions";
				$r = ExecRequete($q);
				while($s = LigneSuivante($r)){
					if(isset(${"commissions_".$s->nom}) && strlen(${"commissions_".$s->nom})) $membre->commissions[$s->id] = ${"commissions_".$s->nom};
				}
				$membre->valide = (check_admin("commission_technique")) ? $membre->valide : 0;
				$membre->insert();
			}
		}
		if($ok){
			echo "<div id = 'action'></div>\n";
			echo "<script type = 'text/javascript'>list_membres(1,1)</script>\n";
		}
	}
	pied();
}
?>
